ALTER TABLE Promotion
DROP CONSTRAINT DF__Promotion__Today__09746778
GO
ALTER TABLE Promotion
DROP COLUMN TodaysCode
GO
ALTER PROCEDURE [dbo].[spAPI_Business_GetPromotionDetails]
(
	@Token VARCHAR(255),
	@PromotionId INT
)
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	DECLARE @IdResult INT
	DECLARE @BusinessId INT
	
	DECLARE @Result XML
	
	SET @IdResult = 0
	EXEC spAPI_Business_CheckToken @Token = @Token, @Result = @IdResult OUT, @BusinessId = @BusinessId OUT
	
	IF @IdResult = 1
		IF EXISTS(SELECT 1 FROM Promotion WHERE BusinessId = @BusinessId AND PromotionId = @PromotionId)
		BEGIN
			SELECT @Result = (SELECT 
									Promotion.[PromotionId],
									Promotion.[BusinessId],
									Promotion.[ProximityRadiusId],
									pr.FromRadius as ProximityFromRadius,
									pr.ToRadius as ProximityToRadius,
									Promotion.[CustomRange],
									Promotion.[Over200MilesFlag],
									Promotion.[RegisteredOutsideOfZip],
									Promotion.[RegisteredOutsideOfState],
									Promotion.[RegisteredOutsideOfCity],
									Promotion.[BaseCostTimingId],
									bct.Timing as BaseCostTimingName,
									Promotion.[BaseCostTimingScheduledAt],
									Promotion.[BaseCostTimingStarts],
									Promotion.[BaseCostTimingEnds],
									Promotion.[CampaignBudget],
									Promotion.[Name],
									Promotion.[PromotionText],
									Promotion.[PromixityRadiusFixedCost],
									Promotion.[PromixityRadiusRatePerPush],									
									Promotion.[CostTimingFixedCost],
									Promotion.[CostTimingRatePerPush],
									Promotion.BaseCostTimingRatePerPush,
									Promotion.BaseCostTimingFixedCost,
									Promotion.[StatusCode],
									Promotion.[FixedCost],
									Promotion.[RatePerPush],
									Promotion.[CheckinCode],
									Promotion.[Deleted],
									Promotion.[Ready],									
									ABS(CHECKSUM(NEWID())) % 1000 AS Checkins,
									
									CASE WHEN (Promotion.RatePerPush = 0) THEN 0
									ELSE CONVERT(INT, FLOOR(Promotion.CampaignBudget / Promotion.RatePerPush)) 
									END AS MaxPushes,
									
									ISNULL(vwPushes.Amount, 0) AS SpentMoney, 
									
									ISNULL(vwPushes.CountPushes, 0) AS SpentPushes,
									
									ISNULL(vwPay.Amount, 0) - ISNULL(vwPushes.Amount, 0) AS UnusedMoney,
									
									CASE WHEN (Promotion.RatePerPush = 0) THEN 0
									ELSE CONVERT(INT, FLOOR((ISNULL(vwPay.Amount, 0) - ISNULL(vwPushes.Amount, 0)) / Promotion.RatePerPush)) 
									END AS UnusedPushes,
									
									case
										when Promotion.[BaseCostTimingId] = 5 then abs(datediff(minute, getdate(), Promotion.[BaseCostTimingEnds]))
										else -1 end as MinutesLeft,
										
									(SELECT Category.CategoryId AS Id, Category.CategoryName AS Name,
											dbo.fnAPI_Category_Path(Category.CategoryId) AS FullPath
										FROM PromotionCategory LEFT JOIN Category ON PromotionCategory.CategoryId = Category.CategoryId
										WHERE PromotionCategory.PromotionId = Promotion.PromotionId
										FOR XML AUTO, TYPE) AS Categories
								FROM Promotion
									left join BaseCostTiming bct on bct.BaseCostTimingId = Promotion.BaseCostTimingId
									left join ProximityRadius pr on pr.ProximityRadiusId = Promotion.ProximityRadiusId,
									(SELECT COUNT(*) AS CountPushes, SUM(RatePerPush) AS Amount
									FROM PromotionPush
									WHERE PromotionId = @PromotionId) AS vwPushes,
									
									(SELECT SUM(Amount) AS Amount
									FROM PromotionPay
									WHERE PromotionId = @PromotionId) AS vwPay
												
								WHERE (Promotion.PromotionId = @PromotionId)
								FOR XML PATH ('Promotion'))
		END								
		ELSE
			SET @IdResult = -1
	
	SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result
END
GO
ALTER PROCEDURE [dbo].[spAPI_Business_GetPromotions]
(
	@Token VARCHAR(255)
)
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	DECLARE @IdResult INT
	DECLARE @BusinessId INT
	
	DECLARE @Result XML
	
	SET @IdResult = 0
	EXEC spAPI_Business_CheckToken @Token = @Token, @Result = @IdResult OUT, @BusinessId = @BusinessId OUT
	
	IF @IdResult = 1
		SELECT @Result = (SELECT Promotion.PromotionId, Promotion.BusinessId, 
							Promotion.ProximityRadiusId, Promotion.CustomRange, Promotion.Over200MilesFlag, 
							Promotion.RegisteredOutsideOfZip, Promotion.RegisteredOutsideOfState, Promotion.RegisteredOutsideOfCity, 
							Promotion.BaseCostTimingId, Promotion.BaseCostTimingScheduledAt, Promotion.BaseCostTimingStarts, Promotion.BaseCostTimingEnds, 
							Promotion.CampaignBudget, Promotion.Name, Promotion.PromotionText, 
							Promotion.PromixityRadiusFixedCost, Promotion.PromixityRadiusRatePerPush, 
							Promotion.CostTimingFixedCost, Promotion.CostTimingRatePerPush, 
							Promotion.StatusCode, Promotion.FixedCost, Promotion.RatePerPush, 
							Promotion.CheckinCode, Promotion.Deleted, Promotion.Ready,
							PromotionStatus.Name AS PromotionStatus, coalesce(vwPush.PushesCount, 0) as PushesCount
							FROM Promotion LEFT JOIN PromotionStatus ON Promotion.StatusCode = PromotionStatus.StatusCode
											LEFT JOIN (SELECT PromotionId, COUNT(*) AS PushesCount
													FROM PromotionPush
													GROUP BY PromotionId) AS vwPush ON Promotion.PromotionId = vwPush.PromotionId
							WHERE (Promotion.BusinessId = @BusinessId)
								AND (Promotion.Deleted = 0)
								AND (Promotion.Ready = 1)
							FOR XML PATH ('Promotion'), ROOT('Promotions'))
	
	SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result
END
GO
ALTER PROCEDURE [dbo].[spAPI_User_GetPromotionDetails]
(
	@Token VARCHAR(255),
	@PromotionId INT
)
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	DECLARE @IdResult INT
	DECLARE @UserId INT
	
	DECLARE @Result XML
	
	SET @IdResult = 0
	EXEC spAPI_User_CheckToken @Token = @Token, @Result = @IdResult OUT, @UserId = @UserId OUT
	
	IF @IdResult = 1
		IF EXISTS(SELECT 1 FROM PromotionPush WHERE UserId = @UserId AND PromotionId = @PromotionId)
		BEGIN
			SELECT @Result = (SELECT 
									Promotion.[PromotionId],
									Promotion.[BusinessId],
									Promotion.[ProximityRadiusId],
									pr.FromRadius as ProximityFromRadius,
									pr.ToRadius as ProximityToRadius,
									Promotion.[CustomRange],
									Promotion.[Over200MilesFlag],
									Promotion.[RegisteredOutsideOfZip],
									Promotion.[RegisteredOutsideOfState],
									Promotion.[RegisteredOutsideOfCity],
									Promotion.[BaseCostTimingId],
									bct.Timing as BaseCostTimingName,
									Promotion.[BaseCostTimingScheduledAt],
									Promotion.[BaseCostTimingStarts],
									Promotion.[BaseCostTimingEnds],
									Promotion.[CampaignBudget],
									Promotion.[Name],
									Promotion.[PromotionText],
									Promotion.[PromixityRadiusFixedCost],
									Promotion.[PromixityRadiusRatePerPush],									
									Promotion.[CostTimingFixedCost],
									Promotion.[CostTimingRatePerPush],
									Promotion.BaseCostTimingRatePerPush,
									Promotion.BaseCostTimingFixedCost,
									Promotion.[StatusCode],
									Promotion.[FixedCost],
									Promotion.[RatePerPush],
									Promotion.[CheckinCode],
									Promotion.[Deleted],
									Promotion.[Ready],									
									ABS(CHECKSUM(NEWID())) % 1000 AS Checkins,
									
									CASE WHEN (Promotion.RatePerPush = 0) THEN 0
									ELSE CONVERT(INT, FLOOR(Promotion.CampaignBudget / Promotion.RatePerPush)) 
									END AS MaxPushes,
									
									ISNULL(vwPushes.Amount, 0) AS SpentMoney, 
									
									ISNULL(vwPushes.CountPushes, 0) AS SpentPushes,
									
									ISNULL(vwPay.Amount, 0) - ISNULL(vwPushes.Amount, 0) AS UnusedMoney,
									
									CASE WHEN (Promotion.RatePerPush = 0) THEN 0
									ELSE CONVERT(INT, FLOOR((ISNULL(vwPay.Amount, 0) - ISNULL(vwPushes.Amount, 0)) / Promotion.RatePerPush)) 
									END AS UnusedPushes,
									
									case
										when Promotion.[BaseCostTimingId] = 5 then abs(datediff(minute, getdate(), Promotion.[BaseCostTimingEnds]))
										else -1 end as MinutesLeft,
										
									(SELECT Category.CategoryId AS Id, Category.CategoryName AS Name,
											dbo.fnAPI_Category_Path(Category.CategoryId) AS FullPath
										FROM PromotionCategory LEFT JOIN Category ON PromotionCategory.CategoryId = Category.CategoryId
										WHERE PromotionCategory.PromotionId = Promotion.PromotionId
										FOR XML AUTO, TYPE) AS Categories
								FROM Promotion
									left join BaseCostTiming bct on bct.BaseCostTimingId = Promotion.BaseCostTimingId
									left join ProximityRadius pr on pr.ProximityRadiusId = Promotion.ProximityRadiusId,
									(SELECT COUNT(*) AS CountPushes, SUM(RatePerPush) AS Amount
									FROM PromotionPush
									WHERE PromotionId = @PromotionId) AS vwPushes,
									
									(SELECT SUM(Amount) AS Amount
									FROM PromotionPay
									WHERE PromotionId = @PromotionId) AS vwPay
												
								WHERE (Promotion.PromotionId = @PromotionId)
								FOR XML PATH ('Promotion'))
		END								
		ELSE
			SET @IdResult = -1
	
	SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result
END
GO